package examplePackage;

import java.sql.Connection;
import java.sql.ResultSet;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class UserDAO
{
	static Connection currentCon = null;
	static ResultSet rs = null; 	
	
	public static UserBean login(UserBean bean) {
    //	preparing some objects for connection
		Statement stmt = null; 
		
		 String username = bean.getUsername();
		 String password = bean.getPassword(); 
		 String searchQuery = "select * from Mydata.staff where username='" + username
		 								+ "' AND password='" + password + "'";
		 // "System.out.println" prints in the console; Normally used to trace the process
		 System.out.println("Your user name is " + username);
		 System.out.println("Your password is " + password);
		 System.out.println("Query: "+searchQuery);
		 
		 try {
			 //connect to DB 
			 currentCon = ConnectionManager.getConnection();
			 stmt=currentCon.createStatement();
			 rs = stmt.executeQuery(searchQuery);
			 boolean more = rs.next();
			 
			 //	if user does not exist set the isValid variable to false 
			 if (!more) {
				 System.out.println("Sorry, you are not a registered user! Please sign up first");
				
				 bean.setValid(false);
			 }
			 //	if user exists set the isValid variable to true 
			 else if (more) 
			 { 
				 String firstName = rs.getString("username");
				 String lastName = rs.getString("password");
				 System.out.println("Welcome " + firstName);
				 bean.setFirstName(firstName);
				 bean.setLastName(lastName);
				 bean.setValid(true);
			 }
		 }
		 catch (Exception ex) 
		 { 
			 System.out.println("Log In failed: An Exception has occurred! " + ex);
		 } 
		 //some exception handling
		 finally 
		 {
			 if (rs != null) 
			 { 
				 try {
					 rs.close();
					 } 
				 catch (Exception e) {}
			 	rs = null;
			 }
		 	 if (stmt != null) 
		 	 { 
		 		 try {
		 			 stmt.close();
		 			 } 
		 		 catch (Exception e) {}
		 		 stmt = null;
		 	 }
		 	if (currentCon != null)
		 	{ 
		 		try {
		 			currentCon.close();
		 			} 
		 		catch (Exception e) { }
		 		currentCon = null;
		 	}
		  
        }

		 	return bean;		
	}	//end of login method
	public static UserBean1 register(UserBean1 bean)
	{
		Statement stmt = null; 
		
		 String cname = bean.getCname();
		 String cid = bean.getCid(); 
		 String email_id=bean.getEmail_id();
		 String phone_no=bean.getPhone_no();
		 String searchQuery ="insert into Mydata.Customer values("+cid+",'"+cname+"',"+phone_no+",'"+email_id+"',"+0+")";
		 
		 System.out.println("Your name is " + cname);
		 System.out.println("Your id is " + cid);
		 System.out.println("Your email_id is " + email_id);
		 System.out.println("Your phone_no is " + phone_no);
		 System.out.println("Query: "+searchQuery);
		
		 
		 try {
			 //connect to DB 
			 currentCon = ConnectionManager.getConnection();
			 stmt=currentCon.createStatement();
			 int gg = stmt.executeUpdate(searchQuery);
			
			 //	if user does not exist set the isValid variable to false 
			 if (gg==0) {
				 System.out.println("Sorry, you are not a registered user! Please sign up first");
				
				 bean.setValid(false);
			 }
			 //	if user exists set the isValid variable to true 
			 else 
			 { 
				 /*String cname1 = rs.getString("cname");
				 String cid1 = rs.getString("cid");
				 String email_id1 =rs.getString("email_id");
				 String phone_no1 =rs.getString("phone_no");*/
				 System.out.println("Registerd Successfully");
				 bean.setCname(cname);
				 bean.setCid(cid);
				 bean.setEmail_id(email_id);
				 bean.setCid(phone_no);
				 bean.setValid(true);
			 }
		 }
		 
		 catch (Exception ex) 
		 { 
			 System.out.println("Registration failed: An Exception has occurred! "); ex.printStackTrace();
		 } 
		 //some exception handling
		 finally 
		 {
			 if (rs != null) 
			 { 
				 try {
					 rs.close();
					 } 
				 catch (Exception e) {}
			 	rs = null;
			 }
		 	 if (stmt != null) 
		 	 { 
		 		 try {
		 			 stmt.close();
		 			 } 
		 		 catch (Exception e) {}
		 		 stmt = null;
		 	 }
		 	if (currentCon != null)
		 	{ 
		 		try {
		 			currentCon.close();
		 			} 
		 		catch (Exception e) { }
		 		currentCon = null;
		 	}
		  
        }//finally ends
		 
		 
		return bean;
	}//end of register method
	
	public static UserBean2 product_Detail(UserBean2 bean)
	{

		Statement stmt = null; 
		
		 String pid = bean.getPid();
		 String pdescription = bean.getPdescription(); 
		 String volume=bean.getVolume();
		 String price=bean.getPrice();
		 String ptype=bean.getPtype();
		 String quantity=bean.getQuantity();
		 String searchQuery ="insert into Mydata.Product values("+pid+",'"+pdescription+"',"+volume+","+price+",'"+ptype+"',"+quantity+")";
		 
		 System.out.println("Your pid is " + pid);
		 System.out.println("Your product is " + pdescription);
		 System.out.println("Your volume is " + volume);
		 System.out.println("Your price is " + price);
		 System.out.println("Your ptype is"+ptype);
		 System.out.println("Your quantity is"+quantity);
		 System.out.println("Query: "+searchQuery);
		
		 
		 try {
			 //connect to DB 
			 currentCon = ConnectionManager.getConnection();
			 stmt=currentCon.createStatement();
			 int gg = stmt.executeUpdate(searchQuery);
			
			 //	if user does not exist set the isValid variable to false 
			 if (gg==0) {
				 System.out.println("Sorry, you are not a registered user! Please sign up first");
				
				 bean.setValid(false);
			 }
			 //	if user exists set the isValid variable to true 
			 else 
			 { 
				 /*String cname1 = rs.getString("cname");
				 String cid1 = rs.getString("cid");
				 String email_id1 =rs.getString("email_id");
				 String phone_no1 =rs.getString("phone_no");*/
				 System.out.println("Products placed successfully");
				 bean.setPid(pid);
				 bean.setPdescription(pdescription);
				 bean.setVolume(volume);
				 bean.setPrice(price);
				 bean.setPtype(ptype);
				 bean.setQuantity(quantity);
				 bean.setValid(true);
			 }
		 }
		 
		 catch (Exception ex) 
		 { 
			 System.out.println("Registration failed: An Exception has occurred! "); ex.printStackTrace();
		 } 
		 //some exception handling
		 finally 
		 {
			 if (rs != null) 
			 { 
				 try {
					 rs.close();
					 } 
				 catch (Exception e) {}
			 	rs = null;
			 }
		 	 if (stmt != null) 
		 	 { 
		 		 try {
		 			 stmt.close();
		 			 } 
		 		 catch (Exception e) {}
		 		 stmt = null;
		 	 }
		 	if (currentCon != null)
		 	{ 
		 		try {
		 			currentCon.close();
		 			} 
		 		catch (Exception e) { }
		 		currentCon = null;
		 	}
		  
        }//finally ends
		 
		 
		return bean;
	}//end of product_detail
	
	public List<UserBean2> viewAllEmployees()
	{
		Statement stmt=null;
		String query = "select * from Mydata.Product";
		 List<UserBean2> list = new ArrayList<UserBean2>();
		 UserBean2 bean =null;
		 try {
	            currentCon = ConnectionManager.getConnection();
	            stmt = currentCon.createStatement();
	            ResultSet rs = stmt.executeQuery(query);
	            while (rs.next()) {
	                bean = new UserBean2();
	                bean.setPid(rs.getString("pid"));
	                bean.setPdescription(rs.getString("pdescription"));
	                bean.setVolume(rs.getString("volume"));
	                bean.setPrice(rs.getString("price"));
	                bean.setPtype(rs.getString("ptype"));
	                bean.setQuantity(rs.getString("quantity"));
	                list.add(bean);
	            }
	            rs.close();
		 }
	            catch (Exception ex) 
	   		 { 
	   			 System.out.println("Registration failed: An Exception has occurred! "); ex.printStackTrace();
	   		 } 
	   		 //some exception handling
	   		 finally 
	   		 {
	   			 if (rs != null) 
	   			 { 
	   				 try {
	   					 rs.close();
	   					 } 
	   				 catch (Exception e) {}
	   			 	rs = null;
	   			 }
	   		 	 if (stmt != null) 
	   		 	 { 
	   		 		 try {
	   		 			 stmt.close();
	   		 			 } 
	   		 		 catch (Exception e) {}
	   		 		 stmt = null;
	   		 	 }
	   		 	if (currentCon != null)
	   		 	{ 
	   		 		try {
	   		 			currentCon.close();
	   		 			} 
	   		 		catch (Exception e) { }
	   		 		currentCon = null;
	   		 	}
	   		  
	           }//finally ends
	            System.out.println(list);
	            return list;	 
	
		 
	}
}
